
**** Prepare data

use "$path\Intermediary Data\localshock_dataforReg_v2.dta",clear

drop if prix_ht==.
drop  j j3
replace d_dieselr=resid_movav_diesel
xtset id daten
gen d_dieselrott=ln(diesel_rotterdam_euro)-ln(l1.diesel_rotterdam_euro)
drop diesel_rotterdam_euro resid_movav_diesel dprix1 

drop d_dieselr
gen d_dieselr=d_dieselrott

merge m:m id using  "$path\Intermediary Data\base_id_suff_stat_6Y"
drop _m

merge m:m id using  "$path\Intermediary Data\base_base_suff_stat_6Y_forinteractkurtfreq"
keep if _m==3
drop _m

bysort id (daten): gen df=log(prix_ht[_n])-log(prix_ht[_n-1]) != 0
bysort id (daten): gen dp=log(prix_ht[_n])-log(prix_ht[_n-1])
gen dp_nonnul=dp if dp!=0

summ daten


levelsof daten, local(levels) 
foreach l of local levels {
preserve
keep if daten==`l'

qui sum dp_nonnul, detail
gen p99=r(p99)
gen p1=r(p1)
replace dp_nonnul=. if dp_nonnul>p99
replace dp_nonnul=. if dp_nonnul<p1

egen freq_time=mean(df)
egen kurtosis_time=kurt(dp_nonnul)
egen mean_time=mean(dp_nonnul)
egen skewness_time=skew(dp_nonnul)
egen sd_time=sd(dp_nonnul)

gen first_obs=1 if _n==1
keep if first_obs==1

keep daten freq_time kurtosis_time mean_time skewness_time sd_time d_dieselr 

if daten==1 {
save "$path\Intermediary Data\base_crosssection_rotterdam_moments", replace
}

else if daten>1 {
append using "$path\Intermediary Data\base_crosssection_rotterdam_moments"
save "$path\Intermediary Data\base_crosssection_rotterdam_moments", replace
}
restore
drop if daten==`l'
display "End of iteration number" `l'

}

******* Run table

clear
use "$path\Intermediary Data\base_crosssection_rotterdam_moments.dta"

sort daten

capture drop p99
capture drop p1
sum freq_time, detail
gen p99=r(p99)
gen p1=r(p1)
replace freq_time=. if freq_time>p99 
replace freq_time=. if freq_time<p1 

capture drop p99
capture drop p1
sum kurtosis_time, detail
gen p99=r(p99)
gen p1=r(p1)
replace kurtosis_time=. if kurtosis_time>p99 
replace kurtosis_time=. if kurtosis_time<p1 

capture drop p99
capture drop p1
sum mean_time, detail
gen p99=r(p99)
gen p1=r(p1)
replace mean_time=. if mean_time>p99 
replace mean_time=. if mean_time<p1 

capture drop p99
capture drop p1
sum skewness_time, detail
gen p99=r(p99)
gen p1=r(p1)
replace skewness_time=. if skewness_time>p99 
replace skewness_time=. if skewness_time<p1 


capture drop p99
capture drop p1
sum sd_time, detail
gen p99=r(p99)
gen p1=r(p1)
replace sd_time=. if sd_time>p99 
replace sd_time=. if sd_time<p1 


gen freq=.
gen kurtosis=.
gen mean=.
gen skewness=.
gen sd=.

reg freq_time d_dieselr, robust
replace freq=_b[d_dieselr] if _n==1
replace freq=_se[d_dieselr] if _n==2
replace freq=e(r2) if _n==3
replace freq=e(N) if _n==4

reg kurtosis_time d_dieselr, robust
replace kurtosis=_b[d_dieselr] if _n==1
replace kurtosis=_se[d_dieselr] if _n==2
replace kurtosis=e(r2) if _n==3
replace kurtosis=e(N) if _n==4

reg mean_time d_dieselr, robust
replace mean=_b[d_dieselr] if _n==1
replace mean=_se[d_dieselr] if _n==2
replace mean=e(r2) if _n==3
replace mean=e(N) if _n==4

reg skewness_time d_dieselr, robust
replace skewness=_b[d_dieselr] if _n==1
replace skewness=_se[d_dieselr] if _n==2
replace skewness=e(r2) if _n==3
replace skewness=e(N) if _n==4

reg sd_time d_dieselr, robust
replace sd=_b[d_dieselr] if _n==1
replace sd=_se[d_dieselr] if _n==2
replace sd=e(r2) if _n==3
replace sd=e(N) if _n==4

keep freq kurtosis mean skewness sd
drop if _n>=5


rename freq col1
rename kurtosis col2
rename mean col3
rename skewness col4
rename sd col5



gen tstatcol1=.
gen tstatcol2=.
gen tstatcol3=.
gen tstatcol4=.
gen tstatcol5=.



foreach i in 1 {
replace tstatcol1=col1[`i']/col1[`i'+1] if _n==`i'
replace tstatcol2=col2[`i']/col2[`i'+1] if _n==`i'
replace tstatcol3=col3[`i']/col3[`i'+1] if _n==`i'
replace tstatcol4=col4[`i']/col4[`i'+1] if _n==`i'
replace tstatcol5=col5[`i']/col5[`i'+1] if _n==`i'
}

gen starscol1=""
gen starscol2=""
gen starscol3=""
gen starscol4=""
gen starscol5=""


foreach i in 1 2 3 4 5{
replace starscol`i'="***" if abs(tstatcol`i')>=2.58 & tstatcol`i'!=.
replace starscol`i'="**" if abs(tstatcol`i')<2.58 & abs(tstatcol`i')>=1.96 & tstatcol`i'!=.
replace starscol`i'="*" if abs(tstatcol`i')<1.96 & abs(tstatcol`i')>=1.64 & tstatcol`i'!=.
}

drop tstat*
rename col* coltemp*

foreach i in 1 2 3 4 5{
replace coltemp`i'=round(coltemp`i', 0.001)
tostring coltemp`i', gen(coltempbis`i') force format("%9.3f")  
egen col`i'=concat(coltempbis`i' starscol`i')
}

rename col1 freq 
rename col2 kurtosis 
rename col3 mean 
rename col4 skewness 
rename col5 sd 


gen Var="Rotterdam" if _n==1
replace Var="R2" if _n==3
replace Var="Nobs" if _n==4

keep Var freq kurtosis mean skewness sd
order Var freq kurtosis mean skewness sd

save "$path\Graphs_Tables\TableA6_edited.dta", replace
